#!/usr/bin/perl
# 
# Read the geographical information of campus map
# Insert data to sqlite database
# Author Hui Liu
use strict;
use warnings;
use DBI;

my $mapFile = $ARGV[0]? $ARGV[0]:"Towson_Buildings_Etc.txt";
my $dbh = DBI->connect("dbi:SQLite:../assets/tourMap.db","","",{RaiseError => 1, sqlite_unicode => 1, AutoCommit => 1});

$dbh->do("Drop table IF EXISTS location_type");
$dbh->do("Drop table IF EXISTS route");
$dbh->do("Drop table IF EXISTS locations");
$dbh->do("Drop table IF EXISTS vistor_parking");

# Create table for type of geo spot
$dbh->do("CREATE TABLE location_type (_id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , tname TEXT, tIndex TEXT)");
$dbh->do("INSERT INTO location_type VALUES (NULL, 'Building', 'B')");
$dbh->do("INSERT INTO location_type VALUES (NULL, 'Parking Lots', 'P')");
$dbh->do("INSERT INTO location_type VALUES (NULL, 'Visitor Parking', 'V')");
$dbh->do("INSERT INTO location_type VALUES (NULL, 'Shuttle Stops', 'S')");

# Create table for shuttle route
$dbh->do("CREATE TABLE route (_id INTEGER PRIMARY KEY NOT NULL , route TEXT)");
$dbh->do("INSERT INTO route VALUES (1, 'Gold Route')");
$dbh->do("INSERT INTO route VALUES (2, 'Goucher Route')");
$dbh->do("INSERT INTO route VALUES (3, 'Kenilworth Route')");
$dbh->do("INSERT INTO route VALUES (4, 'Timonium Route')");
$dbh->do("INSERT INTO route VALUES (5, 'Rodgers Forge Route')");
$dbh->do("INSERT INTO route VALUES (6, 'County Garage')");

$dbh->do("CREATE TABLE locations (_id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , name TEXT, latitude FLOAT, longitude FLOAT, ltype TEXT, details TEXT, history TEXT , rIndex INTEGER, photo TEXT, FOREIGN KEY(rIndex) REFERENCES route(_id))");


open my $fh, $mapFile or die "Can't open file $mapFile";

my $bname;
my $lat;
my $lng;
my $ltype = 'B';

# Insert building and Parking lots
#$dbh->do("INSERT INTO locations VALUES (NULL, $bname, $lat, $lng, $ltype, NULL, NULL, NULL, NULL)");


while (($ltype ne 'V' ) && ( my $line = <$fh>)) {
    chomp $line;
    
    if ($line =~ /^lat/) {
        $line =~ /^lat=\"(.*)\".*lng=\"(.*)\"/;
        #print $&;
        $lat = $1;
        $lng = $2;
    }elsif ($line =~ /^<polygon/) {
        $line =~ /^<polygon name=\"(.*)\"/;
        $bname = $1;
    }elsif ($line =~ /^<\/polygon/){
        $dbh->do("INSERT INTO locations VALUES (NULL, \'$bname\', $lat, $lng, \'$ltype\', NULL, NULL, NULL, NULL)") or die "Couldn't excute statement: " . $dbh->errstr;
    }elsif ($line =~ /^<!-- Parking Lots/){
        $ltype='P';
    }elsif ($line =~ /^<!-- Visitor Parking/){
        $ltype='V';
    }
}

# Insert vistor parking markers
#$dbh->do("CREATE TABLE vistor_parking (_id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , latitude FLOAT, longitude FLOAT)");
while ( ($ltype eq 'V') && (my $line = <$fh>)) {
    chomp $line;
    if ($line =~ /^lat/) {
        $line =~ /^lat=\"(.*)\".*lng=\"(.*)\"/;
        $lat = $1;
        $lng = $2;
        $dbh->do("INSERT INTO locations VALUES (NULL, NULL, $lat, $lng, \'$ltype\', NULL, NULL, NULL, NULL)") or die "Couldn't excute statement: " . $dbh->errstr;
#        $dbh->do("INSERT INTO vistor_parking VALUES (NULL, $lat, $lng)") or die "Couldn't excute statement: " . $dbh->errstr;
    }else {
        $ltype = 'S';
    }
}

# Insert Shuttle Stops
my $rIndex = 1;
while ( my $line = <$fh>) {
    chomp $line;
    if ($line =~ /^<!--/) {
        # Get rIndex of the route
        $line =~ /^<!-- (.*) Shuttle Stops/;
        my $route = $dbh->prepare('SELECT * FROM route WHERE route = ?')
                or die "Couldn't prepare statement: " . $dbh->errstr;
        $route->execute($1) or die "Couldn't execute statement: " . $route->errstr;
        my @row = $route->fetchrow_array();
        $rIndex = $row[0];
    }elsif ($line =~ /lat=/) {
        $line =~ /^\"(.*)\".* lat=\"(.*)\".*lng=\"(.*)\"/;
        $bname = $1;
        $lat = $2;
        $lng = $3;
        $dbh->do("INSERT INTO locations VALUES (NULL, \'$bname\', $lat, $lng, \'$ltype\', NULL, NULL, $rIndex, NULL)") or die "Couldn't excute statement: " . $dbh->errstr;
    }
}




exit;
